Create Staging Table in SSMS And Staging Package in SSIS

Download the SQL File attached to the

Run Script that is attached to create your Staging tables in the Staging Database. Once ran, the tables will be made up of these columns.

*Open SSIS and Create a new project and name it - "BuildingPermitsInMiami"

*Open SSIS Toolbox, Create a new package and name it - "BuildingPermits_Staging" Package.

Next , the following components will be dragged in the Control Flow Dashboard;

Drag Execute SQL Task to Truncate Staging Tables, Double Click and Add Connection manager and Write Sql Query to Truncate Staging Table before every execution to avoid duplicate entries.

Next , Add required Variables, Right Click on Control Flow tab - select Variables from the Menu and Add Variables JSONFileName and File Path and select String Data type and provide path ( location where JSON file is saved in your pc) respectively

Next , add the script task and configure the variables as shown .
Variables can be added by clicking the 3 dots next to Readonlyvariables and selecting the variables as shown

Click edit script and add the following C# script inside Public void main as shown

public void Main()

{

// TODO: Add your code here

string FILEPATH;

string JSONFILENAME;

FILEPATH = Dts.Variables["User::FILEPATH"].Value.ToString();

JSONFILENAME = Path.GetFileName(FILEPATH);

Dts.Variables["User::JSONFILENAME"].Value = JSONFILENAME;

MessageBox.Show(JSONFILENAME);

Dts.TaskResult = (int)ScriptResults.Success;

}

The configuration of the data flow task will be carried on in the next step

The final control flow pane should like this :

How to create basic SSIS package?